package org.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 注意:preparestatement对象,处理预编译的sql,其中的?被称为"预编译占位符",它只能占位sql参数
 * 字段,表名,关键字不能使用预编译占位符来占位
 * select  id,name,gender,age  from t_customer_2023 order by id ? ; 这里会报错,报错信息如下
 * java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for
 * the right syntax to use near ''desc'' at line 1
 *
 * select  id,name,gender,age  from t_customer_2023 where id >  ? ;" 这里正确,没有问题
 */
public class AppPrepareStmt {
    public static void main(String[] args) throws Exception {
        System.out.println("-----------AppPrepareStmt类main方法执行了-------------");

        String jdbcUrl = "jdbc:mysql://localhost:3306/idea?useCursorFetch=true&useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "scyy";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
//            对于mysql-connector-j驱动包,是通过spi自动加载的,不需要手动加载
//            Class.forName("com.mysql.jdbc.Driver");

            // 建立连接
            conn = DriverManager.getConnection(jdbcUrl, username, password);


//            这里会失败,因为排序的 asc 或desc 是sql关键字,不能用预编译占位符
//            String pstmtSql = "select  id,name,gender,age  from t_customer_2023 order by id ? ;";
//            ps = conn.prepareStatement(pstmtSql);
//            ps.setString(1, "desc");

//          这里预编译占位符可以处理sql参数,它没有问题
            String pstmtSql = "select  id,name,gender,age  from t_customer_2023 where id >  ? ;";
            ps = conn.prepareStatement(pstmtSql);
            ps.setString(1, "3");


            rs = ps.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString("id") + "," + rs.getString("name"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
            try {
                if (ps != null) ps.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
            try {
                if (conn != null) conn.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }
}